package com.yi.dao;

import com.yi.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 操作用户
 * @author YI
 * @date 2018-3-19 09:21:51
 */
@Repository
public class UserDao {
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public Integer totalUserInDepartment(Long id) {
        String sql = "select count(1) from user where id=?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, id);
        return count;
    }

    public Integer totalUserInDepartment2(Long id) {
        String sql = "select count(1) from user where department_id=:deptId";
        MapSqlParameterSource namedParameters = new MapSqlParameterSource();
        namedParameters.addValue("id", id);
        Integer count = namedParameterJdbcTemplate
                .queryForObject(sql, namedParameters, Integer.class);
        return count;
    }

    public User findUserById(int userId) {
        String sql = "select * from user where id=?";
        User user = jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));

                return user;
            }
        }, 1);
        return user;
    }

    public List<User> getUserByDepartmentId(int id) {
        String sql = "select * from user where id=? ";
        List<User> user = jdbcTemplate.query(sql, new UserRowMapper(), id);
        return user;
    }

    public Map findUserById2(Integer userId) {
        String sql = "select * from user where id=?";
        return jdbcTemplate.queryForMap(sql, userId);
    }

    public void updateInfo(User user) {
        String sql = "update user set name=? where id = ?";
        jdbcTemplate.update(sql, user.getName(), user.getId());
    }

    public void updateInfoByNamedJdbc(User user) {
        String sql = "update user set name=:name where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(user);
        namedParameterJdbcTemplate.update(sql, source);

    }

    public Integer insertUser(final User user) {
        final String sql = "insert into user (name, gender, goldCoin ) values (?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});
            ps.setString(1, user.getName());
            ps.setInt(2, user.getGender());
            ps.setInt(2, user.getGoldCoin());
            return ps;
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    static class UserRowMapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setGender(rs.getInt("gender"));
            user.setGoldCoin(rs.getInt("goldCoin"));
            return user;
        }

    }
}
